from StoreBilibiliData.CreateTable import uploader, video, comments, dm

from sqlalchemy import select, create_engine, and_, desc
from sqlalchemy.sql.selectable import Select
from sqlalchemy.engine.result import ResultProxy, RowProxy
from sqlalchemy.engine.base import Engine

import datetime


class Uploader:
    uid: int  # 用户id
    name: str  # 用户名

    def __init__(self, _uid: int, _name: str):
        self.uid = _uid
        self.name = _name

    def __str__(self):
        return f'{self.uid=}\n{self.name=}\n'

    @staticmethod
    # 从uid获取一个up主
    def from_uid(_engine: Engine, _uid: int):
        sel = select([uploader.c.uid, uploader.c.name])  # type: Select
        sel = sel.where(uploader.c.uid == _uid)  # type: Select
        rp = _engine.connect().execute(sel)  # type: ResultProxy
        res = rp.first()  # type: RowProxy
        return Uploader(_uid=res.uid, _name=res.name)

    @staticmethod
    # 所有up主的信息
    def get_all(_engine: Engine):
        sel = select([uploader.c.uid, uploader.c.name])
        res = _engine.connect().execute(sel)
        ret = []
        for r in res:
            ret.append(Uploader(_uid=r.uid, _name=r.name))
        return ret


class Video:
    av: int  # av号
    bv: str  # bv号
    comment_count: int  # 评论数
    play_count: int  # 播放数
    title: str  # 视频标题
    description: str  # 视频简介
    uploader_id: int  # up主的uid
    upload_time: datetime.datetime  # 上传时间

    def __init__(self, _av, _bv, _comment_count, _play_count, _title, _description, _uploader_id, _upload_time):
        self.av = _av
        self.bv = _bv
        self.comment_count = _comment_count
        self.play_count = _play_count
        self.title = _title
        self.description = _description
        self.uploader_id = _uploader_id
        self.upload_time = _upload_time

    def __str__(self):
        return f'{self.av=}\n{self.bv=}\n{self.comment_count=}\n{self.play_count=}\n{self.title=}\n' + \
               f'{self.description=}\n{self.uploader_id=}\n{self.upload_time=}\n'

    @staticmethod
    # 使用av号或者bv号获得单个视频信息
    def from_av_or_bv(_engine: Engine, _av: int = -1, _bv: str = ''):
        sel = select([video])
        if _av != -1:
            sel = sel.where(video.c.av == _av)
        elif _bv != '':
            sel = sel.where(video.c.bv == _bv)
        else:
            return None
        res = _engine.connect().execute(sel)
        res = res.first()
        return Video(
            _av=res.av,
            _bv=res.bv,
            _comment_count=res.comment_count,
            _play_count=res.play_count,
            _title=res.title,
            _description=res.description,
            _uploader_id=res.uploader_id,
            _upload_time=res.upload_time,
        )

    @staticmethod
    # 从up主的uid返回它的所有视频
    def from_uploader_id(_engine: Engine, _uid: int):
        sel = select([video]).where(video.c.uploader_id == _uid)
        res = _engine.connect().execute(sel)
        ret = []
        for r in res:
            ret.append(Video(
                _av=r.av,
                _bv=r.bv,
                _comment_count=r.comment_count,
                _play_count=r.play_count,
                _title=r.title,
                _description=r.description,
                _uploader_id=r.uploader_id,
                _upload_time=r.upload_time,
            ))
        return ret

    @staticmethod
    # 返回一个时间段内的视频，起止时间用datetime.datetime表示
    def from_time_period(_engine: Engine, start_time: datetime.datetime, end_time: datetime.datetime) -> list:
        if start_time > end_time:
            start_time, end_time = end_time, start_time

        sel = select([video])  # type: Select
        sel = sel.where(and_(video.c.upload_time >= start_time, video.c.upload_time <= end_time))
        res = _engine.connect().execute(sel)
        ret = []
        for r in res:
            ret.append(Video(
                _av=r.av,
                _bv=r.bv,
                _comment_count=r.comment_count,
                _play_count=r.play_count,
                _title=r.title,
                _description=r.description,
                _uploader_id=r.uploader_id,
                _upload_time=r.upload_time,
            ))
        return ret

    @staticmethod
    # 获得所有视频信息
    def get_all(_engine: Engine):
        res = _engine.connect().execute(select([video]))
        ret = []
        for r in res:
            ret.append(Video(
                _av=r.av,
                _bv=r.bv,
                _comment_count=r.comment_count,
                _play_count=r.play_count,
                _title=r.title,
                _description=r.description,
                _uploader_id=r.uploader_id,
                _upload_time=r.upload_time,
            ))
        return ret


class Comments:
    rp_id: int  # 这个评论的if
    video_id: int  # 对应的视频的av号
    likes: int  # 赞同数
    root_comment: int  # 如果是评论下的回复，这个东西就是那条评论的rp_id，如果不是评论下的回复，这个值是-1
    content: str  # 评论内容
    comment_time: datetime.datetime  # 评论时间

    def __init__(self, _rp_id, _video_id, _likes, _root_comment, _content, _comment_time):
        self.rp_id = _rp_id
        self.video_id = _video_id
        self.likes = _likes
        self.root_comment = _root_comment
        self.content = _content
        self.comment_time = _comment_time

    def __str__(self):
        return f'{self.rp_id=}\n{self.video_id=}\n{self.likes=}\n{self.root_comment=}\n{self.content=}\n' + \
               f'{self.comment_time=}\n'

    @staticmethod
    # 从av号获取所有评论（不包括评论下的回复）
    def from_video_id_without_replies(_engine: Engine, _av: int, _order_by_time: bool = False):
        sel = select([comments]).where(and_(comments.c.video_id == _av, comments.c.root_comment == -1))
        if _order_by_time:
            sel = sel.order_by(desc(comments.c.comment_time))
        else:
            sel = sel.order_by(desc(comments.c.likes))
        res = _engine.connect().execute(sel)
        ret = []
        for r in res:
            ret.append(Comments(
                _rp_id=r.rp_id,
                _video_id=r.video_id,
                _likes=r.likes,
                _root_comment=r.root_comment,
                _content=r.content,
                _comment_time=r.comment_time,
            ))
        return ret

    @staticmethod
    # 从rp_id获得一个评论下的所有回复
    def get_replies(_engine: Engine, _rp_id: int, _order_by_time: bool = False):
        sel = select([comments]).where(comments.c.root_comment == _rp_id)
        sel = sel.order_by(desc(comments.c.root_comment if _order_by_time else comments.c.likes))
        res = _engine.connect().execute(sel)
        ret = []
        for r in res:
            ret.append(Comments(
                _rp_id=r.rp_id,
                _video_id=r.video_id,
                _likes=r.likes,
                _root_comment=r.root_comment,
                _content=r.content,
                _comment_time=r.comment_time,
            ))
        return ret


class Dm:
    video_id: int  # 对应的视频的av号
    content: str  # 内容
    property: str  # 一个字符串，由几个逗号分隔的数字组成，表示这个弹幕的属性，我暂时搞不清楚每个数字的意义

    def __init__(self, _video_id, _content, _property):
        self.video_id = _video_id
        self.content = _content
        self.property = _property

    def __str__(self):
        return f'{self.video_id=}\n{self.content=}\n{self.property=}\n'

    @staticmethod
    # 从av号获取某个视频的弹幕
    def from_video_id(_engine: Engine, _av: int):
        sel = select([dm]).where(dm.c.video_id == _av)
        res = _engine.connect().execute(sel)
        ret = []
        for r in res:
            ret.append(Dm(
                _video_id=r.video_id,
                _content=r.content,
                _property=r.property,
            ))
        return ret


if __name__ == '__main__':
    engine = create_engine('sqlite:///../bilibili.db', echo=True, encoding='utf-8')  # type: Engine
    # up = Uploader.from_uid(_engine=engine, _uid=10330740)
    # print(up.name)
    #
    # ups = Uploader.get_all(_engine=engine)
    # for up in ups:
    #     print(up.uid, up.name)
    #
    # v = Video.from_av_or_bv(_engine=engine, _bv='BV1Vf4y1m74E')
    # print(v)
    # vs = Video.from_uploader_id(_engine=engine, _uid=10330740)
    # for v in vs:
    #     print(v)
    # vs = Video.get_all(_engine=engine)
    # for v in vs:
    #     print(v)
    #
    # cs = Comments.from_video_id_without_replies(_engine=engine, _av=795400970)
    # for c in cs:
    #     print(c)
    # cs = Comments.get_replies(_engine=engine, _rp_id=2818828748)
    # for c in cs:
    #     print(c)

    dms = Dm.from_video_id(_engine=engine, _av=625582281)
    for d in dms:
        print(d)
